package bookmanagementsystem.dao;

import bookmanagementsystem.model.BorrowingRecord;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Timestamp;

public class BorrowingDAO {
    private static final String URL = "jdbc:mysql://localhost:3306/book_management_system";
    private static final String USERNAME = "root";
    static final String PASSWORD = "root";

    // 获取数据库连接的私有方法，统一管理连接的获取与异常处理
    private static Connection getConnection() {
        try {
            return DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
            // 抛出运行时异常，方便在调用处知晓数据库连接出现问题，可根据实际情况做更细致的提示处理
            throw new RuntimeException("数据库连接失败，请检查数据库配置（如地址、端口、用户名、密码等）以及数据库服务是否正常启动。", e);
        }
    }

    // 添加借阅记录到数据库
    public boolean addBorrowing(BorrowingRecord borrowingRecord) {
        String sql = "INSERT INTO borrowing_table (record_id, user_id, book_id, borrowing_time, expected_return_time) VALUES (?,?,?,?,?)";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            preparedStatement.setString(1, borrowingRecord.getRecordID());
            preparedStatement.setString(2, borrowingRecord.getUserID());
            preparedStatement.setString(3, borrowingRecord.getBookID());
            preparedStatement.setTimestamp(4, borrowingRecord.getBorrowingTime());
            preparedStatement.setTimestamp(5, borrowingRecord.getExpectedReturnTime());

            int rowsAffected = preparedStatement.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("添加借阅记录到数据库时出错，错误信息: " + e.getMessage());
            return false;
        }
    }

    // 根据用户ID和图书ID查询借阅记录（用于判断是否已借阅某本图书）
    public BorrowingRecord queryBorrowingByUserAndBook(String userID, String bookID) {
        String sql = "SELECT * FROM borrowing_table WHERE user_id =? AND book_id =?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            // 设置查询参数
            preparedStatement.setString(1, userID);
            preparedStatement.setString(2, bookID);

            // 执行查询
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    String recordID = resultSet.getString("record_id");
                    Timestamp borrowingTime = resultSet.getTimestamp("borrowing_time");
                    Timestamp expected_return_time = resultSet.getTimestamp("expected_return_time");

                    return new BorrowingRecord(recordID, userID, bookID, borrowingTime, expected_return_time);
                }
            }

            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("根据用户ID和图书ID查询借阅记录时出错，错误信息: " + e.getMessage());
            return null;
        }
    }


    // 根据用户ID查询该用户的所有借阅记录
    public List<BorrowingRecord> queryBorrowingRecordsByUser(String userID) {
        List<BorrowingRecord> borrowingRecords = new ArrayList<>();
        String sql = "SELECT * FROM borrowing_table WHERE user_id = ?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            // 设置查询参数
            preparedStatement.setString(1, userID);

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    String recordID = resultSet.getString("record_id");
                    String bookID = resultSet.getString("book_id");
                    Timestamp borrowingTime = resultSet.getTimestamp("borrowing_time");
                    Timestamp expected_return_time = resultSet.getTimestamp("expected_return_time");

                    BorrowingRecord borrowingRecord = new BorrowingRecord(recordID, userID, bookID, borrowingTime, expected_return_time);
                    borrowingRecords.add(borrowingRecord);
                }
            }

            return borrowingRecords;
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("根据用户ID查询所有借阅记录时出错，错误信息: " + e.getMessage());
            return borrowingRecords;
        }
    }

    // 更新图书库存数量（在借阅图书时减少库存数量）
    public boolean updateBookQuantityOnBorrow(String bookID) {
        String sql = "UPDATE book_table SET quantity_in_library = quantity_in_library - 1 WHERE book_id =?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            preparedStatement.setString(1, bookID);

            int rowsAffected = preparedStatement.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("借阅图书时更新图书库存数量失败，错误信息: " + e.getMessage());
            return false;
        }
    }

    // 更新图书库存数量（在归还图书时增加库存数量）
    public boolean updateBookQuantityOnReturn(String bookID) {
        String sql = "UPDATE book_table SET quantity_in_library = quantity_in_library + 1 WHERE book_id =?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            preparedStatement.setString(1, bookID);

            int rowsAffected = preparedStatement.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("归还图书时更新图书库存数量失败，错误信息: " + e.getMessage());
            return false;
        }
    }
}